// mysql 辅助函数
var mysql = require( 'mysql' ),
Util      = require( 'util' ),
Config    = require( '../config/config' ),
conf_db   = Config.database;

// 表名 组装
var fillTn = function( tn ){
    return ( tn.indexOf('@')===0 ? '' : conf_db.prefix ) + tn;
},
// 条件语句 字段组装
join  = function( o ){
    if( typeof o==='undefined' ) o = '';
    var result = '';
    if( Util.isMap( o ) ){
        for(var i in o){
            result += '`'+i+'`='+( typeof o[i]=="number"?o[i]:'"'+o[i]+'"' ) + (o.IsAnd==='and'?' and':o.IsAnd==='or'?' or ':' ,  ');
        }
        result = result.slice( 0, -4 );
    }else if( Array.isArray( o ) ){
        o.forEach(function(_o, i){
            result += _o + ',';
        })
        result = result.slice( 0, -1 );
    }else{
        return o;
    }
    return result;
},
// where 语句的组装  非like语句
whereJoin = function( o ){
    var result = join( o );
    if( result.search(/where/ig)>-1 ){
        return ' ' + result;
    }else if( result.trim().length>2 ){
        return ' WHERE ' + result;
    }else{
        return result;
    }
},
// inert 语句 - 字段处理 现在是单次插入，以后想一下多条记录一次性插入
insertJoin = function( o ){
    if( typeof o==='undefined' ) o = '';
    var result = {field:'(', value:'('};
    // 单条记录，如果以后多条应该是一个array类型
    if( Util.isMap( o ) ){
        for( var i in o ){
            result.field += '`'+ i +'`,';
            result.value += typeof o[i]==='number'?o[i]+',':'"'+o[i]+'",';
        }
        result.field = result.field.slice( 0, -1 ) + ')';
        result.value = result.value.slice( 0, -1 ) + ')';
    }else{
        return o;
    }
    return result;
};

module.exports = {
    connection: function(){
        var connection = mysql.createConnection( conf_db );
        connection.connect(function( err ){
            if( err ){
                throw new TypeError( err );
                return;
            }
        });
        return connection;
    },
    execute: function( type, sql, otbl, res ){
        
        if( type!=='custom' && !otbl.tableName ){
            throw new TypeError( 'database tableName is undefined' );
            return;
        }else if( !otbl.pms ){
            throw new TypeError( 'database pms is undefined' );
            return;
        }else if( !otbl.dataKey ){
            throw new TypeError( 'database dataKey is undefined' );
            return;
        }
        
        var _this  = this,
        connection = _this.connection();
            
        connection.query(sql, function(err, rows, fields) {
            if (err){
                
                otbl.pms && typeof otbl.pms.reject==='function' && otbl.pms.reject( err );
            
            }else{
                
                res.locals[otbl.dataKey||'dbData'] = rows;
                otbl.pms && typeof otbl.pms.resolve==='function' && otbl.pms.resolve( rows );
                
            }
            
            _this.destroy( connection );
            
        });
        
        sql = null;
        
    },
    select: function( otbl, res ){
        
        var sql = 'SELECT * FROM `'+ fillTn( otbl.tableName ) +'`' + whereJoin( otbl.where );
        this.execute( 'select', sql, otbl, res );
        
    },
    update: function( otbl, res ){
        
        var sql    = 'UPDATE `'+ fillTn( otbl.tableName ) +'` SET ' + join( otbl.set ) + whereJoin( otbl.where );
        this.execute( 'update', sql, otbl, res );
        
    },
    delete: function( otbl, res ){
        
        var sql    = 'DELETE FROM `'+ fillTn( otbl.tableName ) +'`' + whereJoin( otbl.where );
        this.execute( 'delete', sql, otbl, res );
            
    },
    insert: function( otbl, res ){
        
        var oInsert = insertJoin( otbl.insert ),
            sql     = 'INSERT INTO `'+ fillTn( otbl.tableName ) +'` ' + oInsert.field + ' VALUES ' + oInsert.value;
        this.execute( 'insert', sql, otbl, res );
        oInsert = sql = null;
        
    },
    custom: function( otbl, res ){
        
        if( !otbl.sql ){
            throw new TypeError( 'the "custom" sql is undefined' );
            return false;
        }
        this.execute( 'custom', otbl.sql, otbl, res );
        
    },
    destroy: function( connection ){
        connection.end();
        //connection.destroy();
    }
}